<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class RollsStatistics extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:RollsStatistics';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '膜统计';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        /*$start_date = $this->argument('start_date');
        $end_date = $this->argument('end_date');
        $type = $this->argument('type');
        if ($type === 'stock') {
            $this->stock();
            return 0;
        }*/
        //$this->statOldData('2016-01', date('Y-m-d'));
        $this->statOldData(date('Y-m'), date('Y-m-d'));
        return 0;
    }

    public function statOldData(string $start_date, string $end_date)
    {
        DB::table('xpel_roll_stat')->where('create_date', '>=', $start_date)->delete();
        $start = strtotime($start_date);
        $end = strtotime($end_date);
        for ($time = $start; $time < $end; $time = strtotime(date('Y-m', $time) . ' +1 month')) {
            $checkDate = date("Y-m-d", $time);
            echo $checkDate; echo PHP_EOL;
            $nextDate = date('Y-m-d', strtotime(date('Y-m', $time) . ' +1 month'));
            $rollSales = DB::table('xpel_dealer_roll_stat')
                ->leftJoin('users', 'users.id', 'xpel_dealer_roll_stat.dealer_id')
                ->where('create_month', '>=', $checkDate)
                ->where('create_month', '<', $nextDate)
                ->select('xpel_dealer_roll_stat.*', 'users.id as user_id', 'users.user_type', 'users.creator_id as parent_user_id')
                ->get();

            $insertDate = date('Y-m-01', strtotime($checkDate));
            if (!empty($rollSales) || $rollSales->isNotEmpty()) {
                foreach($rollSales as $rollSale) {
                    if (empty($rollSale)) {
                        continue;
                    }
                    $sale_1 = in_array($rollSale->product_type_id, [1, 3])  ? $rollSale->num : 0;
                    $sale_2 = $rollSale->product_type_id == 2 ?  $rollSale->num  : 0;
                    $sale_3 = 0;

                    $sale_stat = DB::table('xpel_roll_stat')
                        ->where('user_id', $rollSale->dealer_id)
                        ->where("create_date", $insertDate)
                        ->first();
                    if (empty($rollSale->parent_user_id) && $rollSale->dealer_id != 1) {
                        continue;
                    }
                    $parent_id = $rollSale->dealer_id == 1 ? 0 : ($rollSale->user_type == 2 ? 1 : $rollSale->parent_user_id);
                    if (empty($sale_stat)) {
                        $insertData = [
                            'user_id'   =>  $rollSale->dealer_id,
                            'parent_user_id'=> $parent_id,
                            'create_date'   => $insertDate,
                            'sale_1' => $sale_1,
                            'sale_2' => $sale_2,
                            'sale_3' => $sale_3,
                        ];
                        DB::table('xpel_roll_stat')->insert($insertData);
                    } else {
                        $updateData = [
                            'sale_1' => $sale_stat->sale_1 + $sale_1,
                            'sale_2' => $sale_stat->sale_2 + $sale_2,
                            'sale_3' => $sale_stat->sale_3 + $sale_3,
                        ];
                        DB::table('xpel_roll_stat')->where('id', $sale_stat->id)->update($updateData);
                    }
                }
            }

            $transfers = DB::table('roll_transfer')->leftJoin('roll_master', 'roll_master.id', 'roll_transfer.roll_id')
                ->leftJoin('film_type_master', 'film_type_master.id', 'roll_master.film_type_id')
                ->leftJoin('users', 'users.id', 'roll_transfer.transfer_by_user_id')
                ->where('approved_date', '>=',  $checkDate)
                ->where('approved_date', '<', $nextDate)
                ->select('roll_transfer.*', 'users.user_type', 'users.id as user_id', 'users.creator_id as parent_user_id', 'users.creator_id', 'film_type_master.product_type_id', 'roll_master.film_type_id', 'roll_master.length as master_length')
                ->get();

            if (!empty($transfers) || $transfers->isNotEmpty()) {
                $this->transferData($transfers, $checkDate);
            }

            $returns = DB::table('roll_return')->leftJoin('roll_master', 'roll_master.id', 'roll_return.roll_id')
                ->leftJoin('users', 'users.id', 'roll_return.user_id')
                ->where('roll_return.updated_at', '>=', $checkDate)
                ->where('roll_return.updated_at', '<', $nextDate)
                ->select('roll_return.*', 'users.user_type', 'users.creator_id as parent_user_id', 'roll_master.product_type_id', 'roll_master.film_type_id', 'roll_master.length as master_length')
                ->get();

            if (!empty($returns) || $returns->isNotEmpty()) {
                foreach ($returns as $return) {
                    if ($return->master_length <= 0) {
                        continue;
                    }
                    if (empty($return->parent_user_id)) {
                        continue;
                    }
                    if (in_array($return->film_type_id, [27, 74])) {
                        $len = round($return->length / 1500, 2);
                    } else {
                        $len = round($return->length / $return->master_length, 2);
                    }
                    $return_1 = in_array($return->product_type_id, [1, 3]) ? $len : 0;
                    $return_2 = $return->product_type_id == 2 ? $len : 0;
                    $return_3 = 0;
                    $return_stat = DB::table('xpel_roll_stat')->where('user_id', $return->user_id)
                        ->where("create_date", $insertDate)
                        ->first();
                    $parent_id = $return->user_id == 1 ? 0 : ($return->user_type == 2 ? $return->user_id : $return->parent_user_id);
                    if (empty($return_stat)) {
                        $insertData = [
                            'user_id'   => $return->user_id,
                            'create_date'   => $insertDate,
                            'parent_user_id' => $parent_id,
                            'return_1' => $return_1,
                            'return_2' => $return_2,
                            'return_3' => $return_3,
                        ];
                        DB::table('xpel_roll_stat')->insert($insertData);
                    } else {
                        $updateData = [
                            'return_1' => $return_stat->return_1 + $return_1,
                            'return_2' => $return_stat->return_2 + $return_2,
                            'return_3' => $return_stat->return_3 + $return_3,
                        ];
                        DB::table('xpel_roll_stat')->where('id', $return_stat->id)->update($updateData);
                    }
                    if ($return->user_type == 3) {
                        $return_stat = DB::table('xpel_roll_stat')->where('user_id', $return->parent_user_id)
                            ->where("create_date", $insertDate)
                            ->first();
                        $parent_id = $return->parent_user_id;
                        if (empty($return_stat)) {
                            $insertData = [
                                'user_id'   => $return->parent_user_id,
                                'create_date'   => $insertDate,
                                'parent_user_id' => $parent_id,
                                'return_1' => $return_1,
                                'return_2' => $return_2,
                                'return_3' => $return_3,
                            ];
                            DB::table('xpel_roll_stat')->insert($insertData);
                        } else {
                            $updateData = [
                                'return_1' => $return_stat->return_1 + $return_1,
                                'return_2' => $return_stat->return_2 + $return_2,
                                'return_3' => $return_stat->return_3 + $return_3,
                            ];
                            DB::table('xpel_roll_stat')->where('id', $return_stat->id)->update($updateData);
                        }
                    }
                }
            }
        }
    }

    public function transferData($transfers, string $checkDate)
    {
        $checkDate = date('Y-m-01', strtotime($checkDate));
        foreach ($transfers as $transfer) {
            if ($transfer->master_length <= 0) {
                continue;
            }
            if (in_array($transfer->film_type_id, [27, 74])) {
                $len = round($transfer->length / 1500, 2);
            } else {
                $len = round($transfer->length / $transfer->master_length, 2);
            }
            $transfer_1 = in_array($transfer->product_type_id, [1, 3])  ? $len : 0;
            $transfer_2 = $transfer->product_type_id == 2 ? $len : 0;
            $transfer_3 = 0;
            $parent_id = $transfer->user_id == 1 ? 0 : ($transfer->user_type == 2 ? $transfer->user_id : $transfer->parent_user_id);

            $transferByUser = DB::table('users')->where('id', $transfer->transfer_by_user_id)->select('id', 'user_type', 'creator_id', 'user_type')->first();
            if ($transfer->user_type == 3 || ($transferByUser->id != $transfer->creator_id && $transfer->transfer_to_user_id != $transferByUser->creator_id)) {
                $this->statTransfer($transfer->user_id, $transfer_1, $transfer_2, $transfer_3, $parent_id, $checkDate);
            }
            if ($transfer->user_type == 3 && (($transfer->creator_id != $transferByUser->creator_id && $transfer->user_type == 3) || ($transfer->transfer_to_user_id != $transferByUser->creator_id && $transfer->user_type == 2))) {
                $this->statTransfer($transfer->creator_id, $transfer_1, $transfer_2, $transfer_3, $transfer->creator_id, $checkDate);
            }
        }
    }

    public function statTransfer($user_id, $transfer_1, $transfer_2, $transfer_3, $parent_id, $checkDate)
    {
        $insertDate = date('Y-m-01', strtotime($checkDate));
        $stat = DB::table('xpel_roll_stat')->where('user_id', $user_id)
            ->where("create_date", $insertDate)
            ->first();
        if (empty($stat)) {
            $insertData = [
                'user_id'       => $user_id,
                'create_date'   => $insertDate,
                'transfer_1' => $transfer_1,
                'transfer_2' => $transfer_2,
                'transfer_3' => $transfer_3,
                'parent_user_id' => $parent_id,
            ];
            DB::table('xpel_roll_stat')->insert($insertData);
        } else {
            $updateData = [
                'transfer_1' => $stat->transfer_1 + $transfer_1,
                'transfer_2' => $stat->transfer_2 + $transfer_2,
                'transfer_3' => $stat->transfer_3 + $transfer_3,
            ];
            DB::table('xpel_roll_stat')->where('id', $stat->id)->update($updateData);
        }

    }

    public function stock()
    {
        $page = 1;
        $pageSize = 5000;
        while (true) {
            $pageNow = ($page - 1) * $pageSize;
            echo $pageNow; echo PHP_EOL;
            $stocks = DB::table('roll_restlen')
                ->leftJoin('roll_master', 'roll_master.roll_number', 'roll_restlen.roll_number')
                ->leftJoin('users', 'users.id', 'roll_restlen.user_id')
                ->leftJoin('film_type_master', 'film_type_master.id', 'roll_master.film_type_id')
                ->where('restlen', '>', 0)
                ->offset($pageNow)
                ->limit($pageSize)
                ->select('roll_restlen.*', 'users.user_type', 'users.creator_id as parent_user_id', 'film_type_master.product_type_id', 'roll_master.film_type_id', 'roll_master.length as master_length')
                ->get();
            if (empty($stocks)) {
                break;
            }

            $page++;

            if (!empty($stocks) || $stocks->isNotEmpty()) {
                foreach ($stocks as $stock) {
                    if ($stock->master_length <= 0) {
                        continue;
                    }
                    if (in_array($stock->film_type_id, [27, 74])) {
                        $len = round($stock->restlen / 1500, 2);
                    } else {
                        $len = round($stock->restlen / $stock->master_length, 2);
                    }
                    $stock_1 = in_array($stock->product_type_id, [1, 3]) ? $len : 0;
                    $stock_2 = $stock->product_type_id == 2 ? $len : 0;
                    $stock_3 = 0;
                    $stockData = DB::table('xpel_roll_stat')
                        ->where('user_id', $stock->user_id)
                        ->where("create_date", date('Y-m-d'))
                        ->first();
                    $parent_id = $stock->user_id == 1 ? 0 : ($stock->user_type == 2 ? 1 : $stock->parent_user_id);
                    if (empty($stockData)) {
                        $insertData = [
                            'user_id'   => $stock->user_id,
                            'create_date'   => date('Y-m-d'),
                            'parent_user_id' => $parent_id,
                            'stock_1' => $stock_1,
                            'stock_2' => $stock_2,
                            'stock_3' => $stock_3,
                        ];
                        DB::table('xpel_roll_stat')->insert($insertData);
                    } else {
                        $updateData = [
                            'stock_1' => $stockData->stock_1 + $stock_1,
                            'stock_2' => $stockData->stock_2 + $stock_2,
                            'stock_3' => $stockData->stock_3 + $stock_3,
                        ];
                        DB::table('xpel_roll_stat')->where('id', $stockData->id)->update($updateData);
                    }
                }
            }
        }
    }
}
